﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Entity;

namespace DataAccess
{
    public class CategoriesDA 
    {
        SqlDataAdapter da;
        public DataSet getCategories()
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectCategory", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool CreateCategories(Category c)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("addCategory", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@CategoryName", SqlDbType.NVarChar);
                    command.Parameters["@CategoryName"].Value = c.Category_Name.ToString();

                    command.Parameters.Add("@CategoryType", SqlDbType.NVarChar);
                    command.Parameters["@CategoryType"].Value = c.Category_Type.ToString();

                    command.Parameters.Add("@CategoryDescription", SqlDbType.NVarChar);
                    command.Parameters["@CategoryDescription"].Value = c.Category_Description.ToString();

                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public DataSet getCategoriesByType(string type)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectCategoryByType", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add(new SqlParameter("@CategoryType", type));
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }


        public Category getCategoriesByID(int id)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("selectCategoryByID", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@CategoryID", id));
                    SqlDataReader dr = cmd.ExecuteReader();
                    Category c = null;
                    if (dr.Read())
                    {
                        int cid = Int32.Parse(dr["CategoryID"].ToString());
                        String cateName = dr["CategoryName"].ToString();
                        String cateType = dr["CategoryType"].ToString();
                        String cateDesc = dr["CategoryDescription"].ToString();
                        c = new Category(cid, cateName, cateType, cateDesc);
                    }
                    connection.Close();
                    return c;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool UpdateCategories(Category c)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("updateCategory", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@CategoryID", SqlDbType.NVarChar);
                    command.Parameters["@CategoryID"].Value = c.Category_ID.ToString();

                    command.Parameters.Add("@CategoryName", SqlDbType.NVarChar);
                    command.Parameters["@CategoryName"].Value = c.Category_Name.ToString();

                    command.Parameters.Add("@CategoryType", SqlDbType.NVarChar);
                    command.Parameters["@CategoryType"].Value = c.Category_Type.ToString();

                    command.Parameters.Add("@CategoryDescription", SqlDbType.NVarChar);
                    command.Parameters["@CategoryDescription"].Value = c.Category_Description.ToString();

                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public bool DeleteCategory(Category c)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("removeCategory", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@CategoryID", c.Category_ID));
                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }
    }
}
